with t1 as (select
                /*+ BROADCAST(x) */
                /*+ BROADCAST(y) */
                /*+ BROADCAST(m) */
                t.id,
                t.waybill_id,
                nvl(t.subscribe_source_code, t.order_source_code) order_source_code,
                nvl(t.subscribe_source_name, t.order_source_name) order_source_name,
                null as                                           config_id,
                t.sender_province_id,
                t.sender_province_name,
                t.sender_city_id,
                t.sender_city_name,
                t.sender_area_id,
                t.sender_area_name,
                t.pick_network_code,
                x.name                       pick_network_name,
                x.fran_code                  franchisees_code,
                x.fran_name                  franchisees_name,
                t.proxy_area_code,
                t.proxy_area_name,
                null as                      pick_staff_code,--t.pick_staff_code,
                null as                      pick_staff_name,--t.pick_staff_name,
                y.order_source_group_id,
                y.order_source_group_name,
                t.customer_code,
                t.customer_name,
                t.input_time,
                case
                    when m.PLATE_FLAG is not null and n.MAIL_NO is not null then from_unixtime(
                                unix_timestamp(n.input_time) + 86400, 'yyyy-MM-dd HH:mm:ss')
                    else t.pick_end_time end pick_end_time, -- 截止时间 默认比下单时间多一天
                t.pick_time,
                t.cancel_time,
                case
                    when m.plate_flag is not null and n.mail_no is not null then n.input_time
                    else null end            store_send_time, -- 下单时间
                m.PLATE_FLAG,
                t.order_status_code,
                t.dt
            from jms_dwd.dwd_yl_oms_oms_order_hf t
                     left join jms_dim.dim_network_whole_massage x -- 关联网点资料取出揽收网点的 网点名称、加盟商code及加盟商name
                               on t.pick_network_code = x.code
                     left join jms_dim.dim_tab_order_source_group_hi y
                               on nvl(t.subscribe_source_code, t.order_source_code) = y.ORDER_SOURCE_CODE
                     left join ( -- 关联配置表，取出需要PLATE_FLAG 商家发货判定的来源
                select r.order_source_code,
                       r.order_source_name,
                       r.sender_province_id,
                       r.PLATE_FLAG
                from jms_dim.dim_tab_order_pick_rate_hi r -- 关联及时揽收表，PLATE_FLAG为1表示配置
                where PLATE_FLAG = 1
                  and r.is_delete = 1) m
                               on nvl(t.subscribe_source_code, t.order_source_code) = m.order_source_code and
                                  t.sender_province_id = m.sender_province_id
                     left join (select b.MAIL_NO, -- 关联线下订单表 取商家发货时间
                                       min(b.input_time) input_time
                                from jms_dwd.dwd_yl_oms_order_tb_exporder_hi b
                                where dt >= date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd-HH')
                                  and dt < date_format(date_add('{{ execution_date | cst_ds }}',1 ), 'yyyy-MM-dd-HH')
                                  and b.MAIL_NO like 'JT%'
                                group by b.MAIL_NO) n on n.mail_no = t.waybill_id
            where t.dt >= date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd-HH')
              and t.dt < date_format(date_add('{{ execution_date | cst_ds }}', 1 ), 'yyyy-MM-dd-HH')
              and if(nvl(t.subscribe_source_code, t.order_source_code) = 'D801', n.input_time is not null, 1 = 1)
              and (nvl(t.order_status_code, -1) != 104 or
                   t.cancel_time > concat(date(date_add('{{ execution_date | cst_ds }}', 0)), ' 08:00:00'))
              and (upper(t.pick_network_code) <> 'TEST001' or upper(x.parent_network_code) <> 'TEST001')
              and upper(nvl(t.proxy_area_code, '*^^*')) <> 'TEST001')


insert overwrite table jms_dwd.dwd_order_timely_collection_hi partition (dt)
select id,
       waybill_id,
       order_source_code,
       order_source_name,
       config_id,
       sender_province_id,
       sender_province_name,
       sender_city_id,
       sender_city_name,
       sender_area_id,
       sender_area_name,
       pick_network_code,
       pick_network_name,
       franchisees_code,
       franchisees_name,
       proxy_area_code,
       proxy_area_name,
       pick_staff_code,
       pick_staff_name,
       order_source_group_id,
       order_source_group_name,
       customer_code,
       customer_name,
       input_time,      -- 订单录入时间
       pick_end_time,   -- 依据下单时间计算出的 截止时间
       pick_time,       -- 订单的揽收时间
       cancel_time,     -- 订单的取消时间
       store_send_time, -- 下单时间（线下订单表的商家发货时间）
       case
           when x.if_tml_collect = 1 and x.store_send_time is not null and x.pick_time > x.pick_end_time then 1
           when x.if_tml_collect = 1 and x.store_send_time is not null and x.pick_time <= x.pick_end_time then 0
           when x.if_tml_collect = 1 and x.pick_time is not null and x.pick_time > x.pick_end_time and
                x.pick_time <= date_format(`current_timestamp`() , 'yyyy-MM-dd HH:mm:ss') then 1
           when x.if_tml_collect = 1 and x.pick_time is null or
                x.pick_time > date_format(`current_timestamp`(), 'yyyy-MM-dd HH:mm:ss') then 2
           else 0
           end taking_type,
       plate_flag,
       order_status_code,
       if_tml_collect,  -- 是否及时揽收 1：未及时  0：及时
       dt
from (select id,
             waybill_id,
             order_source_code,
             order_source_name,
             config_id,
             sender_province_id,
             sender_province_name,
             sender_city_id,
             sender_city_name,
             sender_area_id,
             sender_area_name,
             pick_network_code,
             pick_network_name,
             franchisees_code,
             franchisees_name,
             proxy_area_code,
             proxy_area_name,
             pick_staff_code,
             pick_staff_name,
             order_source_group_id,
             order_source_group_name,
             customer_code,
             customer_name,
             input_time,                                                                                                  -- 订单录入时间
             pick_end_time,                                                                                               -- 依据下单时间计算出的 截止时间
             pick_time,                                                                                                   -- 订单的揽收时间
             cancel_time,                                                                                                 -- 订单的取消时间
             store_send_time,                                                                                             -- 下单时间（线下订单表的商家发货时间）
             plate_flag,
             order_status_code,
             `if`((pick_time is null or pick_time > pick_end_time), 1,
                  0)           as                                                                         if_tml_collect, -- 是否及时揽收 1：未及时  0：及时
             -- substr(dt, 0, 10) as                                                                         dt  -- 转换成 年月日格式
             nvl(date_format(pick_end_time, 'yyyy-MM-dd'), '9999-99-99') as dt
      from t1) x where dt>= date_add('{{ execution_date | cst_ds }}', -1) distribute by dt, PMOD(HASH(RAND()),20);